/**
 * 照片数据模型
 * 负责照片相关的数据库操作
 */

import { dbConnection } from '../connection'
import { Photo, PhotoListResponse, PhotoFilters } from '../../renderer/types'

/**
 * 照片模型类
 */
export class PhotoModel {
  /**
   * 插入新照片
   * @param photo 照片数据
   * @returns 插入的照片ID
   */
  static insert(photo: Omit<Photo, 'id'>): number {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      INSERT INTO photos (
        file_path, file_name, file_size, file_hash, width, height, format,
        created_at, modified_at, captured_at, camera_make, camera_model,
        latitude, longitude, altitude, thumbnail_path, preview_path,
        rating, description
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `)
    
    const result = stmt.run(
      photo.file_path,
      photo.file_name,
      photo.file_size,
      photo.file_hash,
      photo.width,
      photo.height,
      photo.format,
      photo.created_at,
      photo.modified_at,
      photo.captured_at,
      photo.camera_make,
      photo.camera_model,
      photo.latitude,
      photo.longitude,
      photo.altitude,
      photo.thumbnail_path,
      photo.preview_path,
      photo.rating,
      photo.description
    )
    
    return result.lastInsertRowid as number
  }

  /**
   * 批量插入照片
   * @param photos 照片数据数组
   * @returns 插入的照片ID数组
   */
  static insertMany(photos: Omit<Photo, 'id'>[]): number[] {
    const db = dbConnection.getConnection()
    const transaction = db.transaction(() => {
      const ids: number[] = []
      for (const photo of photos) {
        ids.push(this.insert(photo))
      }
      return ids
    })
    
    return transaction()
  }

  /**
   * 根据ID获取照片
   * @param id 照片ID
   * @returns 照片数据
   */
  static getById(id: number): Photo | undefined {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT * FROM photos WHERE id = ?')
    return stmt.get(id) as Photo | undefined
  }

  /**
   * 根据文件路径获取照片
   * @param filePath 文件路径
   * @returns 照片数据
   */
  static getByFilePath(filePath: string): Photo | undefined {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT * FROM photos WHERE file_path = ?')
    return stmt.get(filePath) as Photo | undefined
  }

  /**
   * 根据文件哈希获取照片
   * @param fileHash 文件哈希
   * @returns 照片数据
   */
  static getByFileHash(fileHash: string): Photo | undefined {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT * FROM photos WHERE file_hash = ?')
    return stmt.get(fileHash) as Photo | undefined
  }

  /**
   * 获取照片列表
   * @param filters 筛选条件
   * @param page 页码
   * @param limit 每页数量
   * @returns 照片列表和分页信息
   */
  static getList(filters: PhotoFilters = {}, page: number = 1, limit: number = 50): PhotoListResponse {
    const db = dbConnection.getConnection()
    
    // 构建WHERE子句
    const whereConditions: string[] = []
    const params: any[] = []
    
    if (filters.search) {
      whereConditions.push('(file_name LIKE ? OR description LIKE ?)')
      params.push(`%${filters.search}%`, `%${filters.search}%`)
    }
    
    if (filters.dateFrom) {
      whereConditions.push('captured_at >= ?')
      params.push(filters.dateFrom)
    }
    
    if (filters.dateTo) {
      whereConditions.push('captured_at <= ?')
      params.push(filters.dateTo)
    }
    
    if (filters.cameraMake) {
      whereConditions.push('camera_make = ?')
      params.push(filters.cameraMake)
    }
    
    if (filters.cameraModel) {
      whereConditions.push('camera_model = ?')
      params.push(filters.cameraModel)
    }
    
    if (filters.rating !== undefined) {
      whereConditions.push('rating >= ?')
      params.push(filters.rating)
    }
    
    // 构建SQL查询
    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : ''
    
    // 获取总数
    const countStmt = db.prepare(`SELECT COUNT(*) as total FROM photos ${whereClause}`)
    const { total } = countStmt.get(...params) as { total: number }
    
    // 获取照片列表
    const offset = (page - 1) * limit
    const listStmt = db.prepare(`
      SELECT * FROM photos 
      ${whereClause}
      ORDER BY captured_at DESC, created_at DESC
      LIMIT ? OFFSET ?
    `)
    
    const photos = listStmt.all(...params, limit, offset) as Photo[]
    
    return {
      photos,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    }
  }

  /**
   * 更新照片信息
   * @param id 照片ID
   * @param updates 更新数据
   * @returns 是否更新成功
   */
  static update(id: number, updates: Partial<Photo>): boolean {
    const db = dbConnection.getConnection()
    
    const fields = Object.keys(updates).filter(key => key !== 'id')
    if (fields.length === 0) return false
    
    const setClause = fields.map(field => `${field} = ?`).join(', ')
    const values = fields.map(field => (updates as any)[field])
    
    const stmt = db.prepare(`
      UPDATE photos 
      SET ${setClause}
      WHERE id = ?
    `)
    
    const result = stmt.run(...values, id)
    return result.changes > 0
  }

  /**
   * 删除照片
   * @param id 照片ID
   * @returns 是否删除成功
   */
  static delete(id: number): boolean {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('DELETE FROM photos WHERE id = ?')
    const result = stmt.run(id)
    
    return result.changes > 0
  }

  /**
   * 获取照片总数
   * @returns 照片总数
   */
  static getCount(): number {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT COUNT(*) as total FROM photos')
    const { total } = stmt.get() as { total: number }
    
    return total
  }

  /**
   * 获取相机品牌列表
   * @returns 相机品牌列表
   */
  static getCameraMakes(): Array<{ value: string; count: number }> {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      SELECT camera_make as value, COUNT(*) as count 
      FROM photos 
      WHERE camera_make IS NOT NULL AND camera_make != ''
      GROUP BY camera_make
      ORDER BY count DESC
    `)
    
    return stmt.all() as Array<{ value: string; count: number }>
  }

  /**
   * 获取相机型号列表
   * @param make 相机品牌
   * @returns 相机型号列表
   */
  static getCameraModels(make: string): Array<{ value: string; count: number }> {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      SELECT camera_model as value, COUNT(*) as count 
      FROM photos 
      WHERE camera_make = ? AND camera_model IS NOT NULL AND camera_model != ''
      GROUP BY camera_model
      ORDER BY count DESC
    `)
    
    return stmt.all(make) as Array<{ value: string; count: number }>
  }
}